﻿Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmPaymentsVsCommissionsReports

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Me.ErrProv.Clear()
        If Me.RBranch.Checked = True And Me.CombBranch.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombBranch, "Please select a valid branch from the list")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim CondBranch, ColDate, ColName As String

                If Me.RBranch.Checked = True Then
                    CondBranch = " and Branch=N'" & Me.CombBranch.Text & "' "
                Else
                    CondBranch = ""
                End If

                If Me.RCheqDate.Checked = True Then
                    ColDate = "CheqDate"
                    ColName = "Cheq. Date"

                ElseIf Me.RReceiptDate.Checked = True Then
                    ColDate = "ReceiptDate"
                    ColName = "Receipt Date"

                ElseIf Me.RTransDate.Checked = True Then
                    ColDate = "TransDate"
                    ColName = "Trans. Date (Entry Date)"
                End If

                Dim dap As New SqlDataAdapter("Select SNo,ClientID,ClientName,InsurCompany,PolicyNo,PaymentDetails," & _
                                              "TotalIn,TransDate," & _
                                              "dbo.GetPolicyCompCommPerc(ClientID,PolicyNo) CompCommPerc, " & _
                                              "dbo.GetPolicyProdCommPerc(ClientID,PolicyNo) ProdCommPerc, " & _
                                              "dbo.GetPolicyProducer(ClientID,PolicyNo) Producer " & _
                                              "From ClientsAcc " & _
                                              "Where PaymentDetails Not In (N'Refund Endors.',N'Addition Endors.') and " & _
                                              "TotalOut=0 " & CondBranch & _
                                              "and " & ColDate & ">=N'" & Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & _
                                              "and " & ColDate & "<=N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:23:59' " & _
                                              "Order By " & ColDate, cnn)
                Dim das As New DataSet

                dap.Fill(das, "ClientsAcc")

                If Me.RInsurComp.Checked = True Then
                    ShowPaymentsVsCommissionsByInsurComp(das, ColName)

                ElseIf Me.RProducer.Checked = True Then
                    ShowPaymentsVsCommissionsByProducer(das, ColName)

                ElseIf Me.RAll.Checked = True Then
                    ShowPaymentsVsCommissionsByAll(das, ColName)
                End If

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Sub ShowPaymentsVsCommissionsByInsurComp(ByVal das As DataSet, ByVal ColName As String)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New PaymentVsCommissionsByInsurComp
            rpt.SetDataSource(das)

            'Create Parameters

            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'ColDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("ColDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = ColName
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFRm.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'Branch PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Branch")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If Me.RBranch.Checked = True Then
                crParameterDiscreteValue.Value = Me.CombBranch.Text
            Else
                crParameterDiscreteValue.Value = "ALL"
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowPaymentsVsCommissionsByProducer(ByVal das As DataSet, ByVal ColName As String)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New PaymentVsCommissionsByProducer
            rpt.SetDataSource(das)

            'Create Parameters

            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'ColDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("ColDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = ColName
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFRm.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'Branch PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Branch")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If Me.RBranch.Checked = True Then
                crParameterDiscreteValue.Value = Me.CombBranch.Text
            Else
                crParameterDiscreteValue.Value = "ALL"
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowPaymentsVsCommissionsByAll(ByVal das As DataSet, ByVal ColName As String)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim rpt As New PaymentVsCommissionsALL
            rpt.SetDataSource(das)

            'Create Parameters

            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'ColDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("ColDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = ColName
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFRm.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'Branch PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Branch")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If Me.RBranch.Checked = True Then
                crParameterDiscreteValue.Value = Me.CombBranch.Text
            Else
                crParameterDiscreteValue.Value = "ALL"
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub RBranch_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RBranch.CheckedChanged
        If Me.RBranch.Checked = True Then
            Me.CombBranch.Enabled = True

        ElseIf Me.RAllBranch.Checked = True Then
            Me.CombBranch.Enabled = False

        End If
    End Sub

    Sub FillBranches()
        Try
            Me.Cursor = Cursors.WaitCursor

            Me.CombBranch.Items.Clear()
            Dim Branches As ArrayList = GetBranches()

            For Each Branch As String In Branches
                Me.CombBranch.Items.Add(Branch)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmClientsAccPaymentsReports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillBranches()
    End Sub
End Class